"""In this module we define the different tables of our DB.
"""

import sqlalchemy as sa
from cibynet.model import meta


ips = sa.Table("ips", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("address", sa.types.String(32), nullable=False, unique=True),
    sa.Column("available", sa.types.Boolean, nullable=False),
    )


buildings = sa.Table("buildings", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("label", sa.types.String(32), nullable=False),
    )


machines = sa.Table("machines", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("user_id", sa.types.Integer, sa.ForeignKey('users.id')),
    sa.Column("hostname", sa.types.String(32), nullable=False),
    sa.Column("macaddress", sa.types.String(24), nullable=False),
    sa.Column("active", sa.types.Boolean, nullable=False, index=True),
    sa.Column("last_connection", sa.types.DateTime), 
      # Last time seen by the switch or the server (differ dhcp and switch?)
    )


rooms_users = sa.Table('rooms_users', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id')),
    sa.Column('room_id', sa.types.Integer, sa.ForeignKey('rooms.id')),
    sa.Column('from_date', sa.types.DateTime, nullable=False),
    sa.Column('to_date', sa.types.DateTime, default=None),
    )


rooms = sa.Table("rooms", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("building_id", sa.types.Integer, sa.ForeignKey('buildings.id')),
    sa.Column("label", sa.types.Unicode(32), nullable=False),
    )


users = sa.Table("users", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("firstname", sa.types.Unicode(100), nullable=False),
    sa.Column("lastname", sa.types.Unicode(100), nullable=False),
    sa.Column("username", sa.types.Unicode(100), nullable=False, unique=True),
    sa.Column("email", sa.types.Unicode(100), nullable=False),
    sa.Column("phonenumber", sa.types.Integer),
    sa.Column("password", sa.types.Unicode(100)),
    # We need to keep the first username to compute the hash of the password:
    sa.Column("first_username", sa.types.Unicode(100), nullable=False),
    sa.Column("from_old_db", sa.types.Boolean, default=False),
    sa.Column("active", sa.types.Boolean, index=True, nullable=False),
    )


groups = sa.Table("groups", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("name", sa.types.Unicode(100), unique=True, nullable=False),
    sa.Column("level", sa.types.Integer, unique=True, nullable=False),
    )


users_groups = sa.Table("users_groups", meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('user_id', sa.types.Integer, sa.ForeignKey('users.id'),
              nullable=False),
    sa.Column('group_id', sa.types.Integer, sa.ForeignKey('groups.id'), 
              nullable=False),
    )


failed_login_attempts = sa.Table("failed_login_attempts", meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('ip', sa.types.String(32), nullable=False),
    sa.Column('date', sa.types.DateTime, nullable=False),
    sa.Column('login', sa.types.String(100), nullable=False),
    )


login_temporizing = sa.Table("login_temporizing", meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('ip', sa.types.String(32), nullable=False),
    sa.Column('date', sa.types.DateTime, nullable=False),
    )


machines_ips = sa.Table('machines_ips', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('machine_id', sa.types.Integer, sa.ForeignKey('machines.id'),
              nullable=False),
    sa.Column('ip_id', sa.types.Integer, sa.ForeignKey('ips.id'), 
              nullable=False),
    sa.Column('from_date', sa.types.DateTime, nullable=False),
    sa.Column('to_date', sa.types.DateTime, default=None),
    )


switchs = sa.Table("switchs", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("hostname", sa.types.String(100), nullable=False),
    sa.Column("ip_id", sa.types.Integer, sa.ForeignKey('ips.id')),
    sa.Column("manageable", sa.types.Boolean, default=True),
    )


switchports = sa.Table("switchports", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("switch_id", sa.types.Integer, sa.ForeignKey('switchs.id'),
              nullable=False),
    sa.Column("number", sa.types.Integer, nullable=False),
    sa.Column("rooms_id", sa.types.Integer, sa.ForeignKey('rooms.id')),
    )


feedbacks = sa.Table("feedbacks", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("url", sa.types.String(255), nullable=False),
    sa.Column("comment", sa.types.Text, nullable=False),
    sa.Column("user_id", sa.types.Integer, sa.ForeignKey('users.id'), 
              nullable=False),
    sa.Column("date", sa.types.DateTime, nullable=False),
    sa.Column("taken_into_account", sa.Boolean, default=False),
    )


banks = sa.Table("banks", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("name", sa.types.Unicode(100), nullable=False),
    )


checks = sa.Table("checks", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("bank_id", sa.types.Integer, sa.ForeignKey('banks.id'),
              nullable=False),
    sa.Column("number", sa.types.Integer, nullable=False),
    sa.Column("issuer", sa.types.Unicode(100), nullable=False),
    sa.Column("value", sa.types.Float, nullable=False),
    )


registrations = sa.Table("registrations", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("check_id", sa.types.Integer, sa.ForeignKey('checks.id'),
              nullable=False),
    sa.Column("user_id", sa.types.Integer, sa.ForeignKey('users.id'),
              nullable=False),
    sa.Column("room_id", sa.types.Integer, sa.ForeignKey('rooms.id'),
              nullable=False),
    sa.Column("submit_date", sa.types.DateTime, nullable=False),
    sa.Column("confirmation_code", sa.types.String(128), nullable=False),
    # When the user clicked on the confirm link in the email:
    sa.Column("confirmation_date", sa.types.DateTime, nullable=True),
    # When the admin received the check:
    sa.Column("validated_date", sa.types.DateTime, nullable=True),
    sa.Column("validated_by", sa.types.Integer, sa.ForeignKey('users.id')),
    )
